package com.surge;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.criteria.Order;

import org.hibernate.jpa.QueryHints;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class SurgeTest {

	private EntityManagerFactory entityManagerFactory;
	private EntityManager entityManager;
	private EntityTransaction transaction;

	@Before
	public void init() {
		entityManagerFactory = Persistence.createEntityManagerFactory("JPA_cache_m");
		entityManager = entityManagerFactory.createEntityManager();
		transaction = entityManager.getTransaction();
		transaction.begin();
	}

	@After
	public void destroy() {
		transaction.commit();
		entityManager.close();
		entityManagerFactory.close();
	}

	/*
	 * ================================ 具体的业务测试 ================================
	 */

	@SuppressWarnings("unchecked")
	@Test
	public void testHelloJPQL() {
		String jpql = "FROM Customer c WHERE c.age > ?";
		Query query = entityManager.createQuery(jpql);

		// 占位符的索引是从 0 开始
		query.setParameter(0, 1);
		List<Customer> customers = query.getResultList();
		System.out.println(customers.size());
	}

	// 1、默认情况下, 若只查询部分属性, 则将返回 Object[] 类型的结果. 或者 Object[] 类型的 List.
	// 2、new Customer(c.lastName, c.age) 也可以在实体类中创建对应的构造器, 然后再 JPQL
	// 语句中利用对应的构造器返回实体类的对象.
	@SuppressWarnings("unchecked")
	@Test
	public void testPartlyProperties() {
		String jpql = "SELECT new Customer(c.lastName, c.age) FROM Customer c WHERE c.id > ?";
		List<Customer> result = entityManager.createQuery(jpql).setParameter(1, 1).getResultList();

		System.out.println(result);
	}

	// createNamedQuery 适用于在实体类前使用 @NamedQuery 标记的查询语句
	@Test
	public void testNamedQuery() {
		Query query = entityManager.createNamedQuery("testNamedQuery").setParameter(1, 3);
		Customer customer = (Customer) query.getSingleResult();

		System.out.println(customer);
	}

	// createNativeQuery 适用于本地 SQL
	@Test
	public void testNativeQuery() {
		String sql = "SELECT * FROM jpa_cutomers WHERE id = ?";
		Query query = entityManager.createNativeQuery(sql).setParameter(1, 1);

		Object result = query.getSingleResult();
		System.out.println(result);
	}

	// 使用 hibernate 的查询缓存.
	@SuppressWarnings("unchecked")
	@Test
	public void testQueryCache() {
		String jpql = "FROM Customer c WHERE c.age > ?";
		Query query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true);

		// 占位符的索引是从 0 开始
		query.setParameter(0, 1);
		List<Customer> customers = query.getResultList();
		System.out.println(customers.size());

		query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true);

		// 占位符的索引是从 0 开始
		query.setParameter(0, 1);
		customers = query.getResultList();
		System.out.println(customers.size());
	}

	@SuppressWarnings("unchecked")
	@Test
	public void testOrderBy() {
		String jpql = "FROM Customer c WHERE c.age > ? ORDER BY c.age DESC";
		Query query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true);

		// 占位符的索引是从 1 开始
		query.setParameter(0, 1);
		List<Customer> customers = query.getResultList();
		System.out.println(customers.size());
	}

	// 查询 order 数量大于 2 的那些 Customer
	@SuppressWarnings("unchecked")
	@Test
	public void testGroupBy() {
		String jpql = "SELECT c.lastName FROM Customer c " + "GROUP BY c.age " + "HAVING count(c.id) >= 2";
		List<Customer> customers = entityManager.createQuery(jpql).getResultList();

		System.out.println(customers);
	}

	/**
	 * JPQL 的关联查询同 HQL 的关联查询.
	 */
	@Test
	public void testLeftOuterJoinFetch() {
		String jpql = "FROM Customer c LEFT OUTER JOIN FETCH c.orders WHERE c.id = ?";

		Customer customer = (Customer) entityManager.createQuery(jpql).setParameter(0, 1).getSingleResult();
		System.out.println(customer.getLastName());
		System.out.println(customer.getOrders().size());

		// List<Object[]> result =
		// entityManager.createQuery(jpql).setParameter(1, 12).getResultList();
		// System.out.println(result);
	}

	@SuppressWarnings("unchecked")
	@Test
	public void testSubQuery() {
		// 查询所有 Customer 的 lastName 为 YY 的 Order
		String jpql = "SELECT o FROM Order o " + "WHERE o.customer = (SELECT c FROM Customer c WHERE c.lastName = ?)";

		Query query = entityManager.createQuery(jpql).setParameter(0, "YY");
		List<Order> orders = query.getResultList();
		System.out.println(orders.size());
	}

	// 可以使用 JPQL 完成 UPDATE 和 DELETE 操作.
	@Test
	public void testExecuteUpdate() {
		String jpql = "UPDATE Customer c SET c.lastName = ? WHERE c.id = ?";
		Query query = entityManager.createQuery(jpql).setParameter(0, "YYY").setParameter(1, 12);

		query.executeUpdate();
	}

	// 使用 jpql 内建的函数
	@SuppressWarnings("unchecked")
	@Test
	public void testJpqlFunction() {
		String jpql = "SELECT lower(c.email) FROM Customer c";

		List<String> emails = entityManager.createQuery(jpql).getResultList();
		System.out.println(emails);
	}

}
